package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.IngredientsType;
import com.wanmait.cookbook.vo.IngredientsTypeSecond;
import com.wanmait.cookbook.vo.Pager;
import com.wanmait.demo.util.DBHelper;

/*
 * 椋熸潗鍒嗙被浜岀骇琛―AO
 */
public class IngredientsTypeSecondDAO {
	private IngredientsTypeSecondDAO() {}
	private static IngredientsTypeSecondDAO ingredientsTypeSecondDAO = new IngredientsTypeSecondDAO();
	public static IngredientsTypeSecondDAO getIngredientsTypeSecondDAO() {
		return ingredientsTypeSecondDAO;
	}
//	//鐐瑰嚮鎵惧埌鐨勯鏉愯缁嗕俊鎭�
//	public IngredientsTypeSecond getDetailFood(String name) {
//		IngredientsTypeSecond detailFood=null;
//		Connection conn=null;
//		PreparedStatement ps=null;
//		ResultSet rs=null;
//		try {
//			String sql="SELECT * FROM ingredients_type_second WHERE title like ?";
//			conn=DBHelper.getHelper().getConn();
//			ps=conn.prepareStatement(sql);
//			ps.setString(1, "%"+name+"%");
//			rs=ps.executeQuery();
//			if(rs.next()) {
//				detailFood=new IngredientsTypeSecond();
//				detailFood.setId(rs.getInt("id"));
//				detailFood.setPic(rs.getString("pic"));
//				detailFood.setTitle(rs.getString("title"));
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		finally {
//			DBHelper.getHelper().closeConn(rs, ps, conn);
//		}
//		
//		
//		return detailFood;
//		
//	}
//	
	
	
	
	//锟斤拷锟斤拷锟斤拷锟叫的讹拷锟斤拷食锟侥凤拷锟斤拷

	public List<IngredientsTypeSecond> findAllIngredientsTypeSecond()
	{
		List<IngredientsTypeSecond> ingredientsTypeSeconds=new ArrayList<>();

		IngredientsType ingredientsType=null;
		IngredientsTypeSecond ingredientsTypeSecond=null;
		Connection conn=null; 
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			StringBuilder sb=new StringBuilder();
			sb.append(" SELECT ingredients_type_second.id,ingredients_type_second.title,ingredients_type.title type_title, ");
			sb.append(" ingredients_type.id type_id,ingredients_type_second.hot,ingredients_type_second.pic  ");
			sb.append(" FROM ingredients_type,ingredients_type_second  ");
			sb.append(" WHERE ingredients_type_second.ingredients_type_id=ingredients_type.id  ");
			sb.append(" AND ingredients_type_second.`enable`=1 ");
			sb.append(" AND ingredients_type.`enable`=1 ");
			sb.append(" AND ingredients_type_second.`hot`=1 ");
			String sql=sb.toString();
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			
			rs=ps.executeQuery();
			while(rs.next()) 
			{
				ingredientsType =new IngredientsType();
				ingredientsType.setId(rs.getInt("type_id"));
				ingredientsType.setTitle(rs.getString("type_title"));
				
				ingredientsTypeSecond=new IngredientsTypeSecond();
				ingredientsTypeSecond.setId(rs.getInt("id"));
				ingredientsTypeSecond.setTitle(rs.getString("title"));
				ingredientsTypeSecond.setIngredientsType(ingredientsType);
				ingredientsTypeSecond.setEnable(true);
				ingredientsTypeSecond.setHot(rs.getBoolean("hot"));
				ingredientsTypeSecond.setPic(rs.getString("pic"));
				ingredientsTypeSeconds.add(ingredientsTypeSecond);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally 
		{
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}

		return ingredientsTypeSeconds;
	}
	
	//锟斤拷锟斤拷锟斤拷锟叫的讹拷锟斤拷食锟侥凤拷锟斤拷  锟斤拷页
		public List<IngredientsTypeSecond> findAllIngredientsTypeSecondPager(Pager pager)
		{
			List<IngredientsTypeSecond> ingredientsTypeSeconds=new ArrayList<>();
			IngredientsType ingredientsType=null;
			IngredientsTypeSecond ingredientsTypeSecond=null;
			Connection conn = null; 
			PreparedStatement ps = null;
			ResultSet rs = null;
			
				StringBuilder sb=new StringBuilder();
				sb.append("SELECT * FROM ingredients_type,ingredients_type_second  ");
				sb.append(" WHERE ingredients_type_second.ingredients_type_id=ingredients_type.id  ");
				sb.append(" AND ingredients_type_second.`enable`=1  ");
				sb.append(" AND ingredients_type.`enable`=1  ");
				sb.append(" LIMIT ?,? ");
				String sql=sb.toString();
			try {
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, pager.getStart());
				ps.setInt(2, pager.getPageSize());
				rs=ps.executeQuery();
				while(rs.next()) 
				{
					ingredientsType =new IngredientsType();
					ingredientsType.setId(rs.getInt("ingredients_type.id"));
					ingredientsType.setTitle(rs.getString("ingredients_type.title"));
					
					ingredientsTypeSecond=new IngredientsTypeSecond();
					ingredientsTypeSecond.setId(rs.getInt("ingredients_type_second.id"));
					ingredientsTypeSecond.setTitle(rs.getString("ingredients_type_second.title"));
					ingredientsTypeSecond.setHot(rs.getBoolean("hot"));
					ingredientsTypeSecond.setPic(rs.getString("pic"));
					ingredientsTypeSecond.setIngredientsType(ingredientsType);
					ingredientsTypeSecond.setEnable(true);
					ingredientsTypeSeconds.add(ingredientsTypeSecond);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
				
			}

			return ingredientsTypeSeconds;
		}
		
		//锟斤拷锟斤拷锟斤拷锟叫的讹拷锟斤拷食锟斤拷锟斤拷锟斤拷
			public int findAllIngredientsTypeSecondRows() {
				Connection conn=null;
				PreparedStatement ps=null;
				ResultSet rs=null;
				try {
					String sql="SELECT count(*) c  FROM ingredients_type_second WHERE ENABLE=1 ";
					conn=DBHelper.getHelper().getConn();
					ps=conn.prepareStatement(sql);
					rs=ps.executeQuery();
					rs.next();
					int rows=rs.getInt("c");
					return rows;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally 
				{
					DBHelper.getHelper().closeConn(rs, ps, conn);
				}
				return 0;
				
			}
			
			//通锟斤拷锟斤拷锟斤拷食锟斤拷id锟斤拷锟矫讹拷锟斤拷食锟斤拷锟斤拷锟斤拷
			public void setIngredientsTypeSecondHotByIngredientsTypeSecondId(int id,int val) {
				String sql="update Ingredients_type_second set hot=? where id=?";
				DBHelper.getHelper().executeUpdate(sql,val,id);
			}
			
			//通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷ID删锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷
			public void deleteIngredientsTypeSecondById(int id) {
				String sql="update ingredients_type_second set enable=0 where id=?";
				DBHelper.getHelper().executeUpdate(sql, id);
			}
			//通锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷IDs删锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟�
			public void deleteIngredientsTypeSecondsByIds(String ids) {
				String sql="update ingredients_type_second set enable=0 where id in("+ids+")";
				DBHelper.getHelper().executeUpdate(sql);
			}
			
			//锟斤拷佣锟斤拷锟斤拷锟斤拷锟�
			public void addIngredientsTypeSeconds(IngredientsTypeSecond ingredientsTypeSecond) {
				String sql="insert into ingredients_type_second(title,ingredients_type_id,hot,pic) values(?,?,?,?)";
				DBHelper.getHelper().executeUpdate(sql
						,ingredientsTypeSecond.getTitle()
						,ingredientsTypeSecond.getIngredientsType().getId()
						,ingredientsTypeSecond.getHot()
						,ingredientsTypeSecond.getPic());
			}
			
			//锟斤拷锟揭讹拷锟斤拷锟斤拷锟酵革拷锟捷讹拷锟斤拷锟斤拷锟斤拷id
			public IngredientsTypeSecond findIngredientsTypeSecondByid(int id) {
				IngredientsTypeSecond ingredientsTypeSecond = null;
				Connection conn = null;
				PreparedStatement ps = null;
				ResultSet rs = null;
				String sql="SELECT * from ingredients_type_second,ingredients_type "
						+ " WHERE ingredients_type_second.id=? "
						+ " AND ingredients_type_second.ingredients_type_id=ingredients_type.id";
				
				try {
					conn=DBHelper.getHelper().getConn();
					ps=conn.prepareStatement(sql);
					ps.setInt(1, id);
					rs=ps.executeQuery();
					
					if(rs.next()) {
						ingredientsTypeSecond = new IngredientsTypeSecond();
						ingredientsTypeSecond.setId(id);
						ingredientsTypeSecond.setTitle(rs.getString("ingredients_type_second.title"));
						IngredientsType ingredientsType = new IngredientsType();
						ingredientsType.setId(rs.getInt("ingredients_type.id"));
						ingredientsType.setTitle(rs.getString("ingredients_type.title"));
						ingredientsType.setEnable(rs.getBoolean("ingredients_type.enable"));
						ingredientsTypeSecond.setIngredientsType(ingredientsType);
						ingredientsTypeSecond.setPic(rs.getString("ingredients_type_second.pic"));
						ingredientsTypeSecond.setHot(rs.getBoolean("ingredients_type_second.hot"));
						ingredientsTypeSecond.setEnable(rs.getBoolean("ingredients_type_second.enable"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}finally {
					DBHelper.getHelper().closeConn(rs, ps, conn);
				}
				return ingredientsTypeSecond;
			}
		
			
			//锟睫改讹拷锟斤拷锟斤拷锟斤拷锟斤拷通锟斤拷id
			public void upadteIngredientsTypeSecondById(IngredientsTypeSecond ingredientsTypeSecond) {
				if(ingredientsTypeSecond.getPic()==null) {
					String sql="UPDATE ingredients_type_second SET title=?,ingredients_type_id=?,hot=? WHERE id=?";
					DBHelper.getHelper().executeUpdate(sql,ingredientsTypeSecond.getTitle(),ingredientsTypeSecond.getIngredientsType().getId(),ingredientsTypeSecond.getHot(),ingredientsTypeSecond.getId());
				}else {
					String sql="UPDATE ingredients_type_second SET title=?,ingredients_type_id=?,hot=?,pic=? WHERE id=?";
					DBHelper.getHelper().executeUpdate(sql,ingredientsTypeSecond.getTitle(),ingredientsTypeSecond.getIngredientsType().getId(),ingredientsTypeSecond.getHot(),ingredientsTypeSecond.getPic(),ingredientsTypeSecond.getId());
				}
			}
			
			//锟斤拷锟斤拷锟斤拷锟斤拷锟截硷拷锟街诧拷锟揭讹拷锟斤拷食锟侥凤拷锟斤拷  锟斤拷页
			public List<IngredientsTypeSecond> findIngredientsTypeSecondByInputPager(Pager pager,String val)
			{
				List<IngredientsTypeSecond> ingredientsTypeSeconds=new ArrayList<>();
				IngredientsType ingredientsType=null;
				IngredientsTypeSecond ingredientsTypeSecond=null;
				Connection conn = null; 
				PreparedStatement ps = null;
				ResultSet rs = null;
				
					StringBuilder sb=new StringBuilder();
					sb.append("SELECT * FROM ingredients_type,ingredients_type_second  ");
					sb.append(" WHERE ingredients_type_second.ingredients_type_id=ingredients_type.id  ");
					sb.append(" AND ingredients_type_second.`enable`=1  ");
					sb.append(" AND ingredients_type_second.title like ?  ");
					sb.append(" AND ingredients_type.`enable`=1  ");
					sb.append(" LIMIT ?,? ");
					String sql=sb.toString();
				try {
					conn=DBHelper.getHelper().getConn();
					ps=conn.prepareStatement(sql);
					ps.setString(1, "%"+val+"%");
					ps.setInt(2, pager.getStart());
					ps.setInt(3, pager.getPageSize());
					rs=ps.executeQuery();
					while(rs.next()) 
					{
						ingredientsType =new IngredientsType();
						ingredientsType.setId(rs.getInt("ingredients_type.id"));
						ingredientsType.setTitle(rs.getString("ingredients_type.title"));
						
						ingredientsTypeSecond=new IngredientsTypeSecond();
						ingredientsTypeSecond.setId(rs.getInt("ingredients_type_second.id"));
						ingredientsTypeSecond.setTitle(rs.getString("ingredients_type_second.title"));
						ingredientsTypeSecond.setHot(rs.getBoolean("hot"));
						ingredientsTypeSecond.setPic(rs.getString("pic"));
						ingredientsTypeSecond.setIngredientsType(ingredientsType);
						ingredientsTypeSecond.setEnable(true);
						ingredientsTypeSeconds.add(ingredientsTypeSecond);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					DBHelper.getHelper().closeConn(rs, ps, conn);
					
				}

				return ingredientsTypeSeconds;
			}
			
			//锟斤拷锟斤拷锟斤拷锟叫的讹拷锟斤拷食锟斤拷锟斤拷锟斤拷
			public int findIngredientsTypeSecondByInputRows(String val) {
				Connection conn=null;
				PreparedStatement ps=null;
				ResultSet rs=null;
				try {
					String sql="SELECT count(*) c  FROM ingredients_type_second WHERE title like ? and ENABLE=1 ";
					conn=DBHelper.getHelper().getConn();
					ps=conn.prepareStatement(sql);
					ps.setString(1, "%"+val+"%");
					rs=ps.executeQuery();
					rs.next();
					int rows=rs.getInt("c");
					return rows;
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally 
				{
					DBHelper.getHelper().closeConn(rs, ps, conn);
				}
				return 0;
				
			}
			
			
}
